# -*- coding: utf-8 -*-
# @Time: 2024/10/24 23:53
# @Author: foxhuty
# @File: newgaokao.py

import pandas as pd
from sympy import symbols, solve
import numpy as np
import os
import time


class NewGaokaoData:
    """
    从2025年起四川采用新高考（3+1+2)模式。这是专门针对新高考模式的政治, 地理, 生物, 化学四门赋分学科而设计的程序，
    以帮助高中年级老师做成绩分析用。还可以计算各班平均分及占比，有效分统计等。
    """
    # 各个等级赋分区间（T1——T2）
    A_T_range = [86, 100]
    B_T_range = [71, 85]
    C_T_range = [56, 70]
    D_T_range = [41, 55]
    E_T_range = [30, 40]

    subjects_good_scores_all = None
    subjects_good_scores_physics = None
    subjects_good_scores_history = None

    # subjects_all = ['总分', '语文', '数学', '英语', '物理', '历史', '化学', '生物', '政治', '地理']
    # subjects_physics = ['总分', '语文', '数学', '英语', '物理', '化学', '生物', '政治', '地理']
    # subjects_history = ['总分', '语文', '数学', '英语', '历史', '生物', '政治', '地理']

    def __init__(self, file):
        if not os.path.isfile(file):
            raise FileNotFoundError('文件路径无效')
        self.file = file
        try:
            excel_file = pd.ExcelFile(self.file)
        except FileNotFoundError as e:
            raise IOError(f'无法读取文件：{e}')
        self.sheet_names = [sheet_name for sheet_name in excel_file.sheet_names if
                            sheet_name in ['物理类', '历史类', '总表']]
        self.data_list = [pd.read_excel(self.file, sheet_name=sheet_name, dtype={'考号': str}) for sheet_name in
                          self.sheet_names]

    def __str__(self):
        return os.path.basename(self.file)

    def get_grade_data(self):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值后生成excel数据表
        :return: None
        """
        data = self.data_list
        data_added = []
        for item in range(len(data)):
            subjects = [col for col in data[item].columns if col in ['政治', '地理', '生物', '化学']]
            print(f'{self.sheet_names[item]}各学科等级人数及卷面分值区间'.center(100, '*'))
            subject_added_data = []
            for subject in subjects:
                data_item = self.get_grade(data[item], subject)
                subject_added_data.append(data_item)
            subjects_added_data = subject_added_data[-1]
            # subjects_added_data = pd.concat(subject_added_data, ignore_index=False,join='inner', axis=1)
            subjects_added_data_cols = [col for col in subjects_added_data.columns if
                                        col in ['语文', '数学', '英语', '物理', '历史', '政治赋值', '地理赋值',
                                                '生物赋值',
                                                '化学赋值']]
            subjects_added_data['赋值总分'] = subjects_added_data[subjects_added_data_cols].sum(axis=1)
            subjects_added_data.sort_values(by='赋值总分', ascending=False, inplace=True)
            subjects_added_data['序号'] = [i + 1 for i in range(len(subjects_added_data))]
            data_added.append(subjects_added_data)
        return data_added

    def get_grade(self, data, subject):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值
        :param data: df数据表
        :param subject: 学科
        :return: 返回获取了赋分等级和赋分值的df数据
        """
        max_score, min_score = self.get_subject_max_min_score(data, subject)
        data[subject + '等级'] = data[subject].apply(lambda x: self.get_level(x, min_score))
        data[subject + '赋值'] = data[subject].apply(
            lambda x: self.get_final_scores(x, min_score, max_score))
        return data

    def get_average(self):
        data = self.data_list
        final_av = []
        for item in range(len(data)):
            subjects_av = [col for col in data[item].columns if
                           col in ['语文', '数学', '英语', '物理', '历史', '政治', '地理', '生物', '化学', '总分']]
            class_av = data[item].groupby('班级')[subjects_av].mean().round(2)
            av_general = data[item][subjects_av].apply(np.nanmean, axis=0).round(2)
            # av_general = data[subjects_av].mean().round(2)

            av_percentage = class_av / av_general.round(2)
            av_percentage = av_percentage.applymap(lambda x: format(x, '.2%'))  # 以百分号显示
            av_percentage = av_percentage.applymap(lambda x: x.replace('nan%', ''))  # 不显示nan%
            # print(av_percentage.columns)
            av_percentage_cols = [col + '占比' for col in av_percentage.columns]
            col_dict = dict(zip(av_percentage.columns, av_percentage_cols))
            av_percentage.rename(columns=col_dict, inplace=True)
            # print(av_percentage.columns)

            class_av.loc['年级平均'] = av_general
            class_av['参考人数'] = data[item]['班级'].value_counts()
            class_av.loc['年级平均', '参考人数'] = class_av['参考人数'].sum()

            final_av_percentage = pd.concat([class_av, av_percentage], axis=1)
            col_first = '参考人数'
            cols_left = [col for col in final_av_percentage.columns if col != col_first]
            final_av_percentage = final_av_percentage[[col_first] + cols_left]
            final_av_percentage.reset_index(inplace=True)
            final_av.append(final_av_percentage)

        return final_av

    def excel_files(self):
        scores_added_data = self.get_grade_data()
        average_added = self.get_average()
        good_scores = self.good_scores()
        with pd.ExcelWriter(self.file.split('.xlsx')[0] + '--成绩分析统计结果.xlsx') as writer:
            for item in range(len(scores_added_data)):
                scores_added_data[item].to_excel(writer, sheet_name=f'{self.sheet_names[item]}-赋分表', index=False,
                                                 float_format='%.2f')
                average_added[item].to_excel(writer, sheet_name=f'{self.sheet_names[item]}-平均分统计', index=False)
            if len(self.sheet_names) == 1:
                good_scores[0].to_excel(writer, sheet_name=f'{self.sheet_names[item]}--有效分')
            else:
                good_scores[0].to_excel(writer, sheet_name='物理类有效分统计')
                good_scores[1].to_excel(writer, sheet_name='历史类有效分统计')

    @staticmethod
    def get_subject_max_min_score(data, subject):
        """
        用于计算获取各等级的卷面分区间（Y1-Y2)
        :param data:
        :param subject:
        :return: 各等级的卷面分区间（Y1-Y2)
        """
        max_score = []
        min_score = []
        subject_num = data[subject].count()
        A_num = int(subject_num * 0.15)
        B_num = int(subject_num * 0.35)
        C_num = int(subject_num * 0.35)
        D_num = int(subject_num * 0.13)
        data.sort_values(by=subject, ascending=False, inplace=True, ignore_index=True)
        # 计算A等级的卷面上下限分值及学生人数
        data_subject_A = data.loc[:A_num - 1, subject]
        A_max = data_subject_A.max()
        A_min = data_subject_A.min()
        max_score.append(A_max)
        min_score.append(A_min)
        final_data_subject_A = data[data[subject] >= A_min]
        # 计算B等级的卷面上下限分值及学生人数
        data_subject_B = data.loc[len(final_data_subject_A):(len(final_data_subject_A) + B_num - 1), subject]
        B_max = data_subject_B.max()
        B_min = data_subject_B.min()
        max_score.append(B_max)
        min_score.append(B_min)
        final_data_subject_B = data[(data[subject] >= B_min) & (data[subject] <= B_max)]
        # 计算C等级的卷面上下限分值及学生人数
        data_subject_C = data.loc[(len(final_data_subject_A) + len(final_data_subject_B)):(
                len(final_data_subject_A) + len(final_data_subject_B) + C_num - 1), subject]
        C_max = data_subject_C.max()
        C_min = data_subject_C.min()
        max_score.append(C_max)
        min_score.append(C_min)
        final_data_subject_C = data[(data[subject] >= C_min) & (data[subject] <= C_max)]
        # 计算D等级的卷面上下限分值及学生人数
        data_subject_D = data.loc[(len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C)):(
                len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + D_num - 1), subject]
        d_max = data_subject_D.max()
        d_min = data_subject_D.min()
        max_score.append(d_max)
        min_score.append(d_min)
        final_data_subject_D = data[(data[subject] >= d_min) & (data[subject] <= d_max)]
        # 计算E等级的卷面上下限分值及学生人数
        final_data_subject_E = data.loc[
                               (len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + len(
                                   final_data_subject_D)):subject_num - 1, subject]
        E_max = final_data_subject_E.max()
        E_min = final_data_subject_E.min()
        max_score.append(E_max)
        min_score.append(E_min)
        print(
            f'{subject}学科A等级人数为：{len(final_data_subject_A)},'
            f'B等级人数为：{len(final_data_subject_B)},'
            f'C等级人数为：{len(final_data_subject_C)},'
            f'D等级人数为：{len(final_data_subject_D)},'
            f'E等级人数为：{len(final_data_subject_E)}')
        print(f'{subject}学科等级卷面Y1的值分别为：{min_score}, Y2的值分别为：{max_score}')
        return max_score, min_score

    def get_final_scores(self, score, min_score, max_score):
        """
        计算获取学生卷面分数的赋分值
        :param score:
        :param min_score:
        :param max_score:
        :return:
        """
        a_t = NewGaokaoData.A_T_range
        b_t = NewGaokaoData.B_T_range
        c_t = NewGaokaoData.C_T_range
        d_t = NewGaokaoData.D_T_range
        e_t = NewGaokaoData.E_T_range
        if score >= min_score[0]:
            score_added = self.get_added_score(score, min_score[0], max_score[0], a_t[0], a_t[1])
            return score_added
        elif score >= min_score[1]:
            score_added = self.get_added_score(score, min_score[1], max_score[1], b_t[0], b_t[1])
            return score_added
        elif score >= min_score[2]:
            score_added = self.get_added_score(score, min_score[2], max_score[2], c_t[0], c_t[1])
            return score_added
        elif score >= min_score[3]:
            score_added = self.get_added_score(score, min_score[3], max_score[3], d_t[0], d_t[1])
            return score_added
        elif score < min_score[3]:
            score_added = self.get_added_score(score, min_score[4], max_score[4], e_t[0], e_t[1])
            return score_added

    def good_scores(self):
        data_list = self.data_list
        sheet_names = self.sheet_names

        if '总表' in sheet_names:
            single_double_list = []
            single_df_all, double_df_all = self.get_single_data(data_list[0], *NewGaokaoData.subjects_good_scores_all)
            single_double = pd.concat([single_df_all, double_df_all], axis=0, keys=['单有效', '双有效'])
            single_double_list.append(single_double)
            return single_double_list
        else:
            single_double_list = []
            data_physics = [data for data in data_list if '物理' in data.columns][0]
            single_df_physics, double_df_physics = self.get_single_data(data_physics,
                                                                        *NewGaokaoData.subjects_good_scores_physics)
            single_double_physics = pd.concat([single_df_physics, double_df_physics], axis=0, keys=['单有效', '双有效'])
            single_double_list.append(single_double_physics)
            data_history = [data for data in data_list if '历史' in data.columns][0]
            single_df_history, double_df_history = self.get_single_data(data_history,
                                                                        *NewGaokaoData.subjects_good_scores_history)
            # single_df_history.sort_index(ascending=True, inplace=True)
            single_double_history = pd.concat([single_df_history, double_df_history], axis=0, keys=['单有效', '双有效'])
            single_double_list.append(single_double_history)
            return single_double_list

    def get_single_data(self, data, *score):
        subjects = [col for col in data.columns if
                    col in ['总分', '语文', '数学', '英语', '物理', '历史', '化学', '生物', '政治', '地理']]
        single_data_list = []
        double_data_list = []
        for i in range(len(subjects)):
            single_data = self.get_single_subject_data(data, subjects[i], score[i])
            double_data = self.get_double_subject_data(data, subjects[i], score[i])
            single_data_list.append(single_data)
            double_data_list.append(double_data)
        single_data = pd.concat(single_data_list, axis=1)
        double_data = pd.concat(double_data_list, axis=1)
        return single_data, double_data

    @staticmethod
    def get_single_subject_data(data, subject, subject_score):
        single_subject_data = data[data[subject] >= subject_score].groupby(['班级'])[subject].count()
        return single_subject_data

    @staticmethod
    def get_double_subject_data(data, subject, total):
        double_subject_data = data[data['总分'] >= total].groupby('班级')[subject].count()
        return double_subject_data

    @staticmethod
    def get_added_score(y, y1, y2, t1, t2):
        """
        计算赋分值的公式：高考赋分方法，其中y为原始卷面得分，t为赋值得分，
        t1和t2为所在等级赋值区间的下限和上限，y1和y2为卷面所在等级分数区间的下限和上限。
        :param y:
        :param y1:
        :param y2:
        :param t1:
        :param t2:
        :return: 一个学生的赋值得分（四舍五入取整）
        """
        t = symbols('t')
        if y == y1:
            scores_added = t1
            return scores_added
        else:
            scores_added = solve((t2 - t) / (t - t1) - (y2 - y) / (y - y1), t)
            return round(scores_added[0])

    @staticmethod
    def get_level(score, min_score):
        if score >= min_score[0]:
            return 'A'
        elif score >= min_score[1]:
            return 'B'
        elif score >= min_score[2]:
            return 'C'
        elif score >= min_score[3]:
            return 'D'
        elif score < min_score[3]:
            return 'E'

    @staticmethod
    def time_use(fn):
        def inner(*args, **kwargs):
            print(f'开始运行主函数{fn.__name__}'.center(106, '_'))
            start_time = time.time()
            result = fn(*args, **kwargs)
            end_time = time.time()
            time_used = end_time - start_time
            print(f'主函数{fn.__name__}结束运行'.center(106, '_'))
            print(f'运行主函数{fn.__name__}共计耗时{round(time_used, 2)}秒')
            return result

        return inner


if __name__ == '__main__':
    file_path = r'D:\data_test\高2026级学生10月考成绩汇总.xlsx'
    # file_path = r'D:\data_test\高2022级零诊成绩测试数据.xlsx'
    NewGaokaoData.subjects_good_scores_all = [80, 60, 80, 40, 50, 40, 40, 50, 40, 400]
    NewGaokaoData.subjects_good_scores_physics = [80, 60, 80, 40, 40, 40, 50, 40, 380]
    NewGaokaoData.subjects_good_scores_history = [80, 60, 80, 50, 40, 40, 50, 400]
    newgaokao = NewGaokaoData(file_path)
    # datasource.get_grade_data()
    # newgaokao.get_average()
    # print(newgaokao)
    # newgaokao.get_physics_single_data()
    # newgaokao.good_scores()
    newgaokao.excel_files()
